package com.vanysoft.common.utils.Excel;

import com.vanysoft.common.utils.exception.VanyExcelException;
import com.vanysoft.framework.exception.VanySoftBaseException;
import com.vanysoft.framework.messagesource.MessageCodeConstant;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 项目名称：messageCenter
 * 类名称：ExcelUtil
 * 类描述：Excel导入导出工具
 * 创建人：SharpClaw_x
 * 创建时间：2017年7月18日 下午9:24:53
 * 修改人  SharpClaw_x
 * 修改时间：2017年7月18日 下午9:24:53
 * 版本信息：@version 1.0
 * Copyright (c) 2017 上海SharpClaw科技股份有限公司-版权所有
 * 修改备注：
 */
public class ExcelUtil {

    /**
     * Function Name               createDifferentVersion
     *
     * @return
     * @throws IOException
     * @description 通过不同的版本创建workbook
     * Modify History:              Date             Programmer       Notes
     * ---------        ---------------  ---------
     * 2017年6月2日 上午11:12:54           SharpClaw_x      Initial
     * *********************************************************************
     */
    public static Workbook createDifferentVersion(MultipartFile file) throws IOException {
        Workbook workbook = null;
        //通过不同版本的excel创建一个workBook
        if (isExcel2003(file.getInputStream())) {
            System.out.println("传入文件：" + file.getOriginalFilename() + "，Excel 2003版本");
            workbook = new HSSFWorkbook(file.getInputStream());
        } else if (isExcel2007(file.getInputStream())) {
            System.out.println("传入文件：" + file.getOriginalFilename() + "，Excel 2007版本");
            workbook = new XSSFWorkbook(file.getInputStream());
        }

        return workbook;
    }

    /**
     * Function Name               createByFactory
     *
     * @return
     * @throws VanySoftBaseException
     * @throws IOException
     * @throws InvalidFormatException
     * @description 通过工厂类创建workbook
     * Modify History:              Date             Programmer       Notes
     * ---------        ---------------  ---------
     * 2017年6月2日 上午11:13:17           SharpClaw_x      Initial
     * *********************************************************************
     */
    public static Workbook createByFactory(MultipartFile file) throws VanySoftBaseException {
        System.out.println("传入文件：" + file.getOriginalFilename() + "，通过工厂类创建Workbook");

        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(file.getInputStream());
        } catch (InvalidFormatException | IOException e) {
            throw new VanySoftBaseException(MessageCodeConstant.ExcelException.TYPE_ERROR);
        }

        return workbook;
    }


    @SuppressWarnings("unchecked")
    public static Workbook exportExample(List<Map<String, Object>> sheetList) {

        Workbook workbook = new XSSFWorkbook();//2007之后（包含）版本的excel,命名为.xlsx
//		Workbook workbook = new HSSFWorkbook();//2003之前（包含）版本的excel,命名为.xls

        //创建表头样式
        Font fontHeader = workbook.createFont();
        /*fontHeader.setBoldweight(Font.BOLDWEIGHT_BOLD);
        CellStyle cstyleHeader = workbook.createCellStyle();
        cstyleHeader.setFont(fontHeader);
        cstyleHeader.setAlignment(CellStyle.ALIGN_CENTER);
        cstyleHeader.setBorderBottom(CellStyle.BORDER_THIN);
        cstyleHeader.setBorderLeft(CellStyle.BORDER_THIN);
        cstyleHeader.setBorderRight(CellStyle.BORDER_THIN);
        cstyleHeader.setBorderTop(CellStyle.BORDER_THIN);
        //差个内奸单元格样式
        Font fontData = workbook.createFont();
        fontData.setBoldweight(Font.BOLDWEIGHT_NORMAL);
        CellStyle cstyleData = workbook.createCellStyle();
        cstyleData.setBorderBottom(CellStyle.BORDER_THIN);
        cstyleData.setBorderLeft(CellStyle.BORDER_THIN);
        cstyleData.setBorderRight(CellStyle.BORDER_THIN);
        cstyleData.setBorderTop(CellStyle.BORDER_THIN);
        cstyleData.setFont(fontData);
        cstyleData.setAlignment(CellStyle.ALIGN_CENTER);*/

//		sheetList包含内容：
//		sheetName
//		sheet数据（List）
        String title = "";
        String[] header = null;
        String[] rowkey = null;
        List<Map<String, Object>> dataRows = null;
        for (Map<String, Object> sheetMap : sheetList) {
            title = (String) sheetMap.get("title");
            header = (String[]) sheetMap.get("header");
            rowkey = (String[]) sheetMap.get("rowkey");
            dataRows = (List<Map<String, Object>>) sheetMap.get("rows");

            // 生成一个表格
            Sheet sheet = workbook.createSheet(title);
            // 设置表格默认列宽度为25个字节
            sheet.setDefaultColumnWidth(25);
            sheet.setDefaultRowHeight((short) 500);
            //crossSheet(sheet, title, header, rowkey, dataRows, cstyleHeader, cstyleData);
        }


        return workbook;
    }

    /**
     * Function Name               crossSheet
     *
     * @param sheet
     * @param title
     * @param headers
     * @param rowkeys
     * @param rows
     * @param cstyleHeader
     * @param cstyleData
     * @description 横向
     * Modify History:              Date             Programmer       Notes
     * ---------        ---------------  ---------
     * 2017年3月23日 下午4:53:04           SharpClaw_x                     Initial
     * *********************************************************************
     */
    @SuppressWarnings({"rawtypes"})
    private static void crossSheet(Sheet sheet, String title, String[] headers, String[] rowkeys, List<Map<String, Object>> rows, CellStyle headerStyle, CellStyle dataStyle) {

        int index = 0;//Sheet行
        Row row = sheet.createRow(index);
        //生成数据头
        for (int i = 0; i < headers.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellStyle(headerStyle);
            cell.setCellValue(headers[i]);
        }

        /** ----------- 生成数据行     ------------*/
        String value = null;
        for (Map data : rows) {
            row = sheet.createRow(++index);
            //获取map的所有需要导入到excel中数据key值
            for (int j = 0; j < rowkeys.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(dataStyle);
                if (data.get(rowkeys[j]) != null) {
                    value = String.valueOf(data.get(rowkeys[j]));
                    cell.setCellValue(value);
                }
            }
        }
    }

    /**
     * Function Name               getCellValue
     *
     * @param cell
     * @return
     * @throws VanyExcelException
     * @description 获取单元格的值冰转化为String类型
     * Modify History:              Date             Programmer       Notes
     * ---------        ---------------  ---------
     * 2017年6月5日 下午3:29:16           SharpClaw_x      Initial
     * *********************************************************************
     */
    public static String getCellValue(Cell cell) throws VanyExcelException {
        if (cell == null) {
            return "";
        }
        String value = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.
                            getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    value = dataFormatter.formatCellValue(cell);
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                if (cell.getBooleanCellValue()) {
                    value = "true";
                } else {
                    value = "false";
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;

            case Cell.CELL_TYPE_ERROR: // 类型错误
                throw new VanyExcelException("EXCEL_ERROR01", "类型错误");
            default:
                throw new VanyExcelException("EXCEL_ERROR02", "未知错误");
        }
        return value;
    }

    public static List<Row> readExcelContent(Sheet sheet) throws VanyExcelException {

        List<Row> rowList = new ArrayList<Row>();

        for (Row row : sheet) {
            if (!isRowEmpty(row)) {
                rowList.add(row);
            }
        }

        return rowList;
    }

    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
                return false;
        }
        return true;
    }

    public static boolean isExcel2003(InputStream is) {
        try {
            new HSSFWorkbook(is);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    public static boolean isExcel2007(InputStream is) {
        try {
            new XSSFWorkbook(is);
        } catch (Exception e) {
            return false;
        }
        return true;
    }

//	public static void main(String[] args) {
//		String path = "D:/BaiduNetdiskDownload/2007.xlsx";
//		try {
//			
//			if (isExcel2003(new FileInputStream(path))) {
//				System.out.println("03版本");
//			}
//			
//			if (isExcel2007(new FileInputStream(path))) {
//				System.out.println("07版本");
//			}	
//		} catch (FileNotFoundException e) {
//			e.printStackTrace();
//		}
//	}
}
